import pymysql

class DatabaseTool(object):
    def __init__(self, passwd, database, host='localhost', user='root', port=3306, charset='utf8'):
        '''
        创建对象时创建一个连接对象
        :param passwd: 链接的密码
        :param database: 链接的数据库名
        :param host: 链接的主机名,默认为本地主机
        :param user: 链接的用户名,默认为root用户
        :param port: 链接的端口号,默认为3306
        :param charset: 链接的编码方式,默认utf8
        '''
        self.con = pymysql.connect(
            host=host,
            user=user,
            password=passwd,
            database=database,
            port=port,
            charset=charset
        )

    def __cur(self, cur=pymysql.cursors.DictCursor):
        cursor = self.con.cursor(cursor=cur)
        return cursor

    def create_table(self, sql):
        '''
        传入建表sql语句,在数据库新建一张表
        :param sql: 建表的sql语句
        :return: 返回是否成功
        '''
        try:
            with self.__cur() as cur:
                cur.execute(sql)
        except Exception as e:
            return e

    def fetchAll(self, sql):
        '''
        根据传入的sql语句查询出结果
        :param sql: 查询的sql语句
        :return: 所有查询结果或错误信息
        '''
        try:
            with self.__cur() as cur:
                cur.execute(sql)
                data = cur.fetchall()
            return data
        except Exception as e:
            return e

    def fetchOne(self, sql):
        '''
        根据传入的sql语句查询出第一条结果
        :param sql: 查询的sql语句
        :return: 一条查询结果或错误信息
        '''
        try:
            with self.__cur() as cur:
                cur.execute(sql)
                data = cur.fetchone()
            return data
        except Exception as e:
            return e

    def fetchMany(self, sql, num):
        '''
        根据传入的sql语句查询出最多指定条数结果
        :param sql: 查询的sql语句
        :param num: 指定的条数限制
        :return: 查询结果或错误信息
        '''
        try:
            with self.__cur() as cur:
                cur.execute(sql)
                data = cur.fetchmany(num)
            return data
        except Exception as e:
            return e

    def execute_one(self, sql, args=None):
        '''
        根据指定sql语句新增|修改|删除一条记录
        :param sql: 删除的sql语句
        :param args: 当sql不完整,有需要用户输入的未知参数时传入调用
        :return: 是否成功
        '''
        try:
            with self.__cur() as cur:
                num = cur.execute(sql, args)
            if num == 1:
                self.con.commit()
                return 'success'
            else:
                self.con.rollback()
                return False
        except Exception as e:
            return e

    def execute_many(self, sql, args):
        '''
        根据指定sql语句新增|修改|删除多条条记录
        :param sql: 删除的sql语句
        :param args: 删除的条件限制
        :return: 是否成功
        '''
        try:
            with self.__cur() as cur:
                num = cur.executemany(sql, args)
            if num == len(args):
                self.con.commit()
                return 'success'
            else:
                self.con.rollback()
                return False
        except Exception as e:
            return e

    def affair(self, args):
        '''
        根据sql语句的集合执行事务
        :param args: 多条sql语句组成的集合
        :return: 成功|失败|错误信息
        '''
        try:
            with self.__cur() as cur:
                num = 0
                for i in args:
                    num1 = cur.execute(i)
                    num += num1
            if num == len(args):
                self.con.commit()
                return 'success'
            else:
                self.con.rollback()
                return False
        except Exception as e:
            return e

    def __del__(self):
        '''
        在文件结束时关闭连接对象
        :return: None
        '''
        self.con.close()


if __name__ == '__main__':
    con = DatabaseTool('jiao', 'stu')
    # data = con.fetchAll('select * from student')
    # print(data)
    data = con.fetchOne('select * from student')
    print(data)
